package org.example.ding.repository;

import org.example.ding.entity.Dish;
import org.example.ding.entity.DishCategory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

/**
 * 菜品数据访问层
 */
@Repository
public interface DishRepository extends JpaRepository<Dish, Long> {

    /**
     * 根据名称查找菜品
     */
    Optional<Dish> findByName(String name);

    /**
     * 根据分类查找菜品
     */
    List<Dish> findByCategory(DishCategory category);

    /**
     * 根据分类ID查找菜品
     */
    List<Dish> findByCategoryId(Long categoryId);

    /**
     * 查找可用的菜品
     */
    List<Dish> findByIsAvailableTrue();

    /**
     * 根据分类查找可用菜品
     */
    List<Dish> findByCategoryAndIsAvailableTrue(DishCategory category);

    /**
     * 根据分类ID查找可用菜品
     */
    List<Dish> findByCategoryIdAndIsAvailableTrue(Long categoryId);

    /**
     * 根据受欢迎程度排序查找菜品
     */
    List<Dish> findByIsAvailableTrueOrderByPopularityScoreDesc();

    /**
     * 根据受欢迎程度排序查找指定数量的菜品
     */
    List<Dish> findTop10ByIsAvailableTrueOrderByPopularityScoreDesc();

    /**
     * 根据点餐次数排序查找热门菜品
     */
    List<Dish> findTop10ByIsAvailableTrueOrderByOrderCountDesc();

    /**
     * 根据名称模糊搜索菜品
     */
    @Query("SELECT d FROM Dish d WHERE d.name LIKE %:keyword% AND d.isAvailable = true")
    List<Dish> searchByName(@Param("keyword") String keyword);

    /**
     * 根据营养成分范围查找菜品
     */
    @Query("SELECT d FROM Dish d WHERE d.calories BETWEEN :minCalories AND :maxCalories " +
           "AND d.isAvailable = true")
    List<Dish> findByCaloriesRange(@Param("minCalories") BigDecimal minCalories, 
                                  @Param("maxCalories") BigDecimal maxCalories);

    /**
     * 根据蛋白质含量范围查找菜品
     */
    @Query("SELECT d FROM Dish d WHERE d.protein BETWEEN :minProtein AND :maxProtein " +
           "AND d.isAvailable = true")
    List<Dish> findByProteinRange(@Param("minProtein") BigDecimal minProtein, 
                                 @Param("maxProtein") BigDecimal maxProtein);

    /**
     * 根据辣度查找菜品
     */
    List<Dish> findBySpiceLevelAndIsAvailableTrue(Dish.SpiceLevel spiceLevel);

    /**
     * 根据烹饪方式查找菜品
     */
    List<Dish> findByCookingMethodAndIsAvailableTrue(Dish.CookingMethod cookingMethod);

    /**
     * 分页查询菜品
     */
    Page<Dish> findByIsAvailableTrue(Pageable pageable);

    /**
     * 根据分类分页查询菜品
     */
    Page<Dish> findByCategoryIdAndIsAvailableTrue(Long categoryId, Pageable pageable);

    /**
     * 复合搜索菜品
     */
    @Query("SELECT d FROM Dish d WHERE " +
           "(:categoryId IS NULL OR d.category.id = :categoryId) AND " +
           "(:keyword IS NULL OR d.name LIKE %:keyword%) AND " +
           "(:spiceLevel IS NULL OR d.spiceLevel = :spiceLevel) AND " +
           "(:cookingMethod IS NULL OR d.cookingMethod = :cookingMethod) AND " +
           "(:minCalories IS NULL OR d.calories >= :minCalories) AND " +
           "(:maxCalories IS NULL OR d.calories <= :maxCalories) AND " +
           "d.isAvailable = true")
    Page<Dish> searchDishes(@Param("categoryId") Long categoryId,
                           @Param("keyword") String keyword,
                           @Param("spiceLevel") Dish.SpiceLevel spiceLevel,
                           @Param("cookingMethod") Dish.CookingMethod cookingMethod,
                           @Param("minCalories") BigDecimal minCalories,
                           @Param("maxCalories") BigDecimal maxCalories,
                           Pageable pageable);

    /**
     * 统计菜品总数
     */
    @Query("SELECT COUNT(d) FROM Dish d WHERE d.isAvailable = true")
    Long countAvailableDishes();

    /**
     * 统计各分类菜品数量
     */
    @Query("SELECT d.category.name, COUNT(d) FROM Dish d WHERE d.isAvailable = true GROUP BY d.category.name")
    List<Object[]> countDishesByCategory();

    /**
     * 获取营养成分统计
     */
    @Query("SELECT AVG(d.calories), AVG(d.protein), AVG(d.carbohydrates), AVG(d.fat) " +
           "FROM Dish d WHERE d.isAvailable = true")
    Object[] getNutritionStatistics();

    /**
     * 检查菜品名称是否存在
     */
    boolean existsByName(String name);

    /**
     * 根据ID列表查找菜品
     */
    List<Dish> findByIdInAndIsAvailableTrue(List<Long> ids);

    /**
     * 更新菜品点餐次数
     */
    @Query("UPDATE Dish d SET d.orderCount = d.orderCount + 1 WHERE d.id = :dishId")
    void incrementOrderCount(@Param("dishId") Long dishId);
}
